<?php
namespace Operation\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Zend\EventManager\EventManagerInterface;
use Zend\View\Model\ViewModel;
use Zend\Session\Container;

class TableaudebordController extends AbstractActionController
{
    protected $saleheadTable;
    protected $salerowTable;
    protected $storeTable;
    protected $meteoTable;
    protected $storecountTable;
    protected $drawercountTable;
    
    public function setEventManager(EventManagerInterface $events)
    {
        parent::setEventManager($events);
 
        $controller = $this;
        $events->attach('dispatch', function ($e) use ($controller) {
            $controller->layout()->setTemplate('layout/operation');
        }, 100);
 
        return $this;
    }
    
    public function getSaleheadTable()
    {
        if (!$this->saleheadTable) {
            $sm = $this->getServiceLocator();
            $this->saleheadTable = $sm->get('Operation\Model\SaleheadTable');
        }
        return $this->saleheadTable;
    }
    
    public function getSalerowTable()
    {
        if (!$this->salerowTable) {
            $sm = $this->getServiceLocator();
            $this->salerowTable = $sm->get('Operation\Model\SalerowTable');
        }
        return $this->salerowTable;
    }
    
    public function getStoreTable()
    {
        if (!$this->storeTable) {
            $sm = $this->getServiceLocator();
            $this->storeTable = $sm->get('Operation\Model\StoreTable');
        }
        return $this->storeTable;
    }
    
    public function getMeteoTable()
    {
        if (!$this->meteoTable) {
            $sm = $this->getServiceLocator();
            $this->meteoTable = $sm->get('Operation\Model\MeteoTable');
        }
        return $this->meteoTable;
    }
    
    public function getStorecountTable()
    {
        if (!$this->storecountTable) {
            $sm = $this->getServiceLocator();
            $this->storecountTable = $sm->get('Operation\Model\StorecountTable');
        }
        return $this->storecountTable;
    }
    
    public function getDrawercountTable()
    {
        if (!$this->drawercountTable) {
            $sm = $this->getServiceLocator();
            $this->drawercountTable = $sm->get('Operation\Model\DrawercountTable');
        }
        return $this->drawercountTable;
    }
    
    public function indexAction()
    {
        $request = $this->getRequest();
        if ($request->isPost()) {
            $query = $this->getQuery($request);
            $meteoQuery = $this->getMeteoQuery($request);
            $ecQuery = $this->getEcartsQuery($request);
            
            // MISE EN SESSION DES CRITERES DANS SELECTION DU PERIMETRE
            $dataCritere = new Container('dataCritere');
            $dataCritere->getManager()->getStorage()->clear('dataCritere');
            
            if($request->getPost()->periodY)
                $dataCritere->periodY = $request->getPost()->periodY;
            if($request->getPost()->periodM)
                $dataCritere->periodM = $request->getPost()->periodM;
            if($request->getPost()->semaine)
                $dataCritere->semaine = $request->getPost()->semaine;
            if($request->getPost()->specific_date)
                $dataCritere->specific_date = $request->getPost()->specific_date;
        
            return new ViewModel(array(
                'monthText' => $this->echoMonthText(),
                
                ///////// PERIODE TEXT
                'periodText' => $query['period'],

                ///////// STORE
                'store' => $this->getStoreTable()->getStore($request->getPost()->boutiques),

                ///////// CHIFFRE D'AFFAIRES
                'caMois' => $this->getSaleheadTable()->getTbCa($query['whereM']),
                'caJour' => $this->getSaleheadTable()->getTbCa($query['whereS']),
                'caTout' => $this->getSaleheadTable()->getTbCa($query['whereT']),

                ///////// TICHET MOYEN
                'tmTtcJour' => $this->getSaleheadTable()->getTbTmTtc($query['whereS']),
                'tmTtcMois' => $this->getSaleheadTable()->getTbTmTtc($query['whereM']),
                'tmTtcTout' => $this->getSaleheadTable()->getTbTmTtc($query['whereT']),
                
                ///////// METEO
                'meteoDay' => $this->getMeteoTable()->getMeteo($meteoQuery['whereS']),
                'meteoMonth' => $this->getMeteoTable()->getMeteos($meteoQuery['whereM']),
                
                ///////// REPAS
                'repasMois' => $this->getSaleheadTable()->getTbRepas($query['whereM']),
                'repasJour' => $this->getSaleheadTable()->getTbRepas($query['whereS']),
                'repasTout' => $this->getSaleheadTable()->getTbRepas($query['whereT']),
                
                ///////// TRANSACTION ET VENTE
                'nbTJour' => $this->getSaleheadTable()->getTbTransaction($query['whereS']),                
                'nbTMois' => $this->getSaleheadTable()->getTbTransaction($query['whereM']),
                'nbTTout' => $this->getSaleheadTable()->getTbTransaction($query['whereT']),
                
                'nbAJour' => $this->getSalerowTable()->getTbNbArticle($query['whereS']),
                'nbAMois' => $this->getSalerowTable()->getTbNbArticle($query['whereM']),
                'nbATout' => $this->getSalerowTable()->getTbNbArticle($query['whereT']),
                
                ///////// ECART CAISSE
                'ecartDay' => $this->getStorecountTable()->getTbEcarts($ecQuery['whereS']),
                'ecartMonth' => $this->getStorecountTable()->getTbEcarts($ecQuery['whereM']),
            ));
        } else {
            return $this->redirect()->toRoute('operation/default');
        }
    }
    
    function getQuery($request) {
        $idstore = ($request->getPost()->boutiques)?$request->getPost()->boutiques:1;
        $whereM = "idStore = ".$idstore;
        $whereS = "idStore = ".$idstore;
        $whereT = "idStore = ".$idstore;
        $whereM2 = "";
        $whereS2 = "";

        if($request->getPost()->periodY){
            $counterA = 1;
            if($request->getPost()->periodM){
                $counterM = 1;                    
                if($request->getPost()->semaine){
                    // Année + Mois + Semaine
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):                                                              
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            }

                            // Récupération du dernier mois
                            if($counterM == count($request->getPost()->periodM)){
                                $lastmonth = $month;
                            } else {
                                $counterM++;
                            }
                        endforeach;

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;

                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".$lastyear." AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth."  AND DAYOFWEEK(orderDate) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } 
                // Année + Mois
                else {
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            }

                            // Récupération du dernier mois
                            if($counterM == count($request->getPost()->periodM)){
                                $lastmonth = $month;
                            } else {
                                $counterM++;
                            }
                        endforeach;

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;

                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".$lastyear." AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth." ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } else {                    
                if($request->getPost()->semaine){ 
                    // Année + Semaine
                    foreach($request->getPost()->periodY as $year):                            
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                        } else {
                            $whereM2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                        }

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = 12";
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".$lastyear." AND EXTRACT(MONTH FROM orderDate) = 12 AND DAYOFWEEK(orderDate) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } else { 
                    // Année
                    foreach($request->getPost()->periodY as $year):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                        } else {
                            $whereM2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                        }

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = 12";
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".$lastyear." AND EXTRACT(MONTH FROM orderDate) = 12 ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } 
        } else {
            if($request->getPost()->periodM){
                $counterM = 1;
                if($request->getPost()->semaine){ 
                    // Mois + Semaine
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( ( EXTRACT(MONTH FROM orderDate) = ".$month.")";
                        } else {
                            $whereM2 .= " OR ( EXTRACT(MONTH FROM orderDate) = ".$month.")";
                        }

                        // Récupération du dernier mois
                        if($counterM == count($request->getPost()->periodM)){
                            $lastmonth = $month;                            
                            $whereM2 .= " )";
                        } else {
                            $counterM++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".date('Y')." AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth." AND DAYOFWEEK(orderDate) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } else { 
                    // Mois
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                        } else {
                            $whereM2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                        }

                        // Récupération du dernier mois
                        if($counterM == count($request->getPost()->periodM)){
                            $lastmonth = $month;
                            $whereM2 .= " )";
                        } else {
                            $counterM++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".date('Y')." AND EXTRACT(MONTH FROM orderDate) = ".$lastmonth." ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } else {
                if($request->getPost()->semaine){  
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM orderDate) = 12";
                    $whereS2 .= " AND DAYOFMONTH(orderDate) = (SELECT MAX(DAYOFMONTH(orderDate)) FROM saleHead WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM orderDate) = ".date('Y')." AND EXTRACT(MONTH FROM orderDate) = 12 AND DAYOFWEEK(orderDate) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            }
        }

        // Si une date spécifique a été définie
        if($request->getPost()->altDate){
            $whereM = "idStore = ".$idstore;
            $whereS = "idStore = ".$idstore;
            $whereT = "idStore = ".$idstore;
            $whereM2 = "";
            $whereS2 = "";

            $whereS2 .= " AND orderDate = '".$request->getPost()->altDate."'";

            if($request->getPost()->periodY){
                $counterA = 0;
                if($request->getPost()->periodM){ ///// Année + Mois
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM orderDate) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM orderDate) = ".$month.")";
                            }
                        endforeach;
                        $counterA++;
                    endforeach;
                } else { ///// Année
                    if($whereM2 == "") {
                        $whereM2 .= " AND ( EXTRACT(YEAR FROM orderDate) = ".$year;
                    } else {
                        $whereM2 .= " OR EXTRACT(YEAR FROM orderDate) = ".$year;
                    }
                    $counterA++;
                }

                if($counterA == count($request->getPost()->periodY)){
                    $whereM2 .= " )";
                }
            } else {
                if($request->getPost()->periodM){ ///// Mois
                    $counterM = 0;
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(MONTH FROM orderDate) = ".$month;
                        } else {
                            $whereM2 .= " OR EXTRACT(MONTH FROM orderDate) = ".$month;
                        }
                        $counterM++;
                    endforeach;

                    if($counterM == count($request->getPost()->periodM)){
                        $whereM2 .= " )";
                    }
                }
            }
        }

        $whereM .= $whereM2;
        $whereS .= $whereS2;

        ///////////////////// PERIODE TEXT ///////////////////////
        $period = "";
        $periodM = "";
        $periodY = "";
        $periodJ = "";
        $monthText = $this->echoMonthText();
        $dayText = $this->echoShortDayText();

        if($request->getPost()->periodM){
            $i = 1;
            foreach($request->getPost()->periodM as $mois):
                if($i < count($request->getPost()->periodM))
                    $periodM .= $monthText[$mois].", ";
                else
                    $periodM .= $monthText[$mois];
                $i++;
            endforeach;
        }
        if($request->getPost()->periodY){
            $i = 1;
            $periodY .= ' ';
            foreach($request->getPost()->periodY as $annee):                    
                if($i < count($request->getPost()->periodY))
                    $periodY .= $annee.", ";
                else
                    $periodY .= $annee;
                $i++;
            endforeach;
        }
        if($request->getPost()->semaine){
            $i = 1;
            if($periodM!="" || $periodY!="")
                $periodJ .= ' - ';

            foreach($request->getPost()->semaine as $jour):                    
                if($i < count($request->getPost()->semaine))
                    $periodJ .= $dayText[$jour]." ";
                else
                    $periodJ .= $dayText[$jour];
                $i++;
            endforeach;
        }

        if($request->getPost()->altDate){
            if($periodM!="" || $periodY!="")
                $periodJ = ' - ';

            $periodJ .= $request->getPost()->specific_date;
        }

        $period = $periodM.$periodY.$periodJ;
        $period = ($period != "")?$period:"Toute la période";

        $query['whereS'] = $whereS;
        $query['whereM'] = $whereM;
        $query['whereT'] = $whereT;
        //$query['groupby'] = $groupby;
        $query['period'] = $period;

        return $query;        
    }
    
    function getMeteoQuery($request) {
        $idstore = ($request->getPost()->boutiques)?$request->getPost()->boutiques:1;
        $whereM = "idStore = ".$idstore;
        $whereS = "idStore = ".$idstore;
        $whereM2 = "";
        $whereS2 = "";

        if($request->getPost()->periodY){
            $counterA = 1;
            if($request->getPost()->periodM){
                $counterM = 1;                    
                if($request->getPost()->semaine){
                    // Année + Mois + Semaine
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):                                                              
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }

                            // Récupération du dernier mois
                            if($counterM == count($request->getPost()->periodM)){
                                $lastmonth = $month;
                            } else {
                                $counterM++;
                            }
                        endforeach;

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;

                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = ".$lastmonth."  AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } 
                // Année + Mois
                else {
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }

                            // Récupération du dernier mois
                            if($counterM == count($request->getPost()->periodM)){
                                $lastmonth = $month;
                            } else {
                                $counterM++;
                            }
                        endforeach;

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;

                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = ".$lastmonth." ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } else {                    
                if($request->getPost()->semaine){ 
                    // Année + Semaine
                    foreach($request->getPost()->periodY as $year):                            
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                        } else {
                            $whereM2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                        }

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = 12";
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = 12 AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } else { 
                    // Année
                    foreach($request->getPost()->periodY as $year):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                        } else {
                            $whereM2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                        }

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = 12";
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = 12 ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } 
        } else {
            if($request->getPost()->periodM){
                $counterM = 1;
                if($request->getPost()->semaine){ 
                    // Mois + Semaine
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month.")";
                        } else {
                            $whereM2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month.")";
                        }

                        // Récupération du dernier mois
                        if($counterM == count($request->getPost()->periodM)){
                            $lastmonth = $month;                            
                            $whereM2 .= " )";
                        } else {
                            $counterM++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".date('Y')." AND EXTRACT(MONTH FROM date) = ".$lastmonth." AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } else { 
                    // Mois
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                        } else {
                            $whereM2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                        }

                        // Récupération du dernier mois
                        if($counterM == count($request->getPost()->periodM)){
                            $lastmonth = $month;
                            $whereM2 .= " )";
                        } else {
                            $counterM++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".date('Y')." AND EXTRACT(MONTH FROM date) = ".$lastmonth." ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } else {
                if($request->getPost()->semaine){  
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = 12";
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM Meteo WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".date('Y')." AND EXTRACT(MONTH FROM date) = 12 AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            }
        }

        // Si une date spécifique a été définie
        if($request->getPost()->altDate){
            $whereM = "idStore = ".$idstore;
            $whereS = "idStore = ".$idstore;
            $whereM2 = "";
            $whereS2 = "";

            $whereS2 .= " AND date = '".$request->getPost()->altDate."'";

            if($request->getPost()->periodY){
                $counterA = 0;
                if($request->getPost()->periodM){ ///// Année + Mois
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }
                        endforeach;
                        $counterA++;
                    endforeach;
                } else { ///// Année
                    if($whereM2 == "") {
                        $whereM2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                    } else {
                        $whereM2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                    }
                    $counterA++;
                }

                if($counterA == count($request->getPost()->periodY)){
                    $whereM2 .= " )";
                }
            } else {
                if($request->getPost()->periodM){ ///// Mois
                    $counterM = 0;
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                        } else {
                            $whereM2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                        }
                        $counterM++;
                    endforeach;

                    if($counterM == count($request->getPost()->periodM)){
                        $whereM2 .= " )";
                    }
                }
            }
        }

        $whereM .= $whereM2;
        $whereS .= $whereS2;

        $query['whereS'] = $whereS;
        $query['whereM'] = $whereM;

        return $query;        
    }
    
    function getEcartsQuery($request) {
        $idstore = ($request->getPost()->boutiques)?$request->getPost()->boutiques:1;
        $whereM = "idStore = ".$idstore;
        $whereS = "idStore = ".$idstore;
        $whereT = "idStore = ".$idstore;
        $whereM2 = "";
        $whereS2 = "";

        if($request->getPost()->periodY){
            $counterA = 1;
            if($request->getPost()->periodM){
                $counterM = 1;                    
                if($request->getPost()->semaine){
                    // Année + Mois + Semaine
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):                                                              
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }

                            // Récupération du dernier mois
                            if($counterM == count($request->getPost()->periodM)){
                                $lastmonth = $month;
                            } else {
                                $counterM++;
                            }
                        endforeach;

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;

                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = ".$lastmonth."  AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } 
                // Année + Mois
                else {
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }

                            // Récupération du dernier mois
                            if($counterM == count($request->getPost()->periodM)){
                                $lastmonth = $month;
                            } else {
                                $counterM++;
                            }
                        endforeach;

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;

                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = ".$lastmonth." ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } else {                    
                if($request->getPost()->semaine){ 
                    // Année + Semaine
                    foreach($request->getPost()->periodY as $year):                            
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                        } else {
                            $whereM2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                        }

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = 12";
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = 12 AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } else { 
                    // Année
                    foreach($request->getPost()->periodY as $year):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                        } else {
                            $whereM2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                        }

                        // Récupération de la derniere année
                        if($counterA == 1){
                            $lastyear = $year;
                        } 

                        if($counterA == count($request->getPost()->periodY)){
                            $whereM2 .= " )";
                        } else {
                            $counterA++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".$lastyear;
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = 12";
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".$lastyear." AND EXTRACT(MONTH FROM date) = 12 ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } 
        } else {
            if($request->getPost()->periodM){
                $counterM = 1;
                if($request->getPost()->semaine){ 
                    // Mois + Semaine
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( ( EXTRACT(MONTH FROM date) = ".$month.")";
                        } else {
                            $whereM2 .= " OR ( EXTRACT(MONTH FROM date) = ".$month.")";
                        }

                        // Récupération du dernier mois
                        if($counterM == count($request->getPost()->periodM)){
                            $lastmonth = $month;                            
                            $whereM2 .= " )";
                        } else {
                            $counterM++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".date('Y')." AND EXTRACT(MONTH FROM date) = ".$lastmonth." AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                } else { 
                    // Mois
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                        } else {
                            $whereM2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                        }

                        // Récupération du dernier mois
                        if($counterM == count($request->getPost()->periodM)){
                            $lastmonth = $month;
                            $whereM2 .= " )";
                        } else {
                            $counterM++;
                        }
                    endforeach;
                    
                    // Le jour du dernier mois de la dernière année
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = ".$lastmonth;
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".date('Y')." AND EXTRACT(MONTH FROM date) = ".$lastmonth." ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            } else {
                if($request->getPost()->semaine){  
                    // Le jour du dernier mois de la dernière année
                    $counterJ = 1;
                    $dayTab = array();
                    
                    foreach($request->getPost()->semaine as $day):
                        $dayTab[] = $day+1;
                    endforeach;
                    
                    $dayline = implode(',', $dayTab);
                    $whereS2 .= " AND ( EXTRACT(YEAR FROM date) = ".date('Y');
                    $whereS2 .= " AND EXTRACT(MONTH FROM date) = 12";
                    $whereS2 .= " AND DAYOFMONTH(date) = (SELECT MAX(DAYOFMONTH(date)) FROM StoreCount WHERE idStore = ".$idstore." AND ( ( EXTRACT(YEAR FROM date) = ".date('Y')." AND EXTRACT(MONTH FROM date) = 12 AND DAYOFWEEK(date) IN (".($dayline).") ) ) ) )"; // (DAYOFWEEK, Dimanche=1 et non 0)
                }
            }
        }

        // Si une date spécifique a été définie
        if($request->getPost()->altDate){
            $whereM = "idStore = ".$idstore;
            $whereS = "idStore = ".$idstore;
            $whereT = "idStore = ".$idstore;
            $whereM2 = "";
            $whereS2 = "";

            $whereS2 .= " AND date = '".$request->getPost()->altDate."'";

            if($request->getPost()->periodY){
                $counterA = 0;
                if($request->getPost()->periodM){ ///// Année + Mois
                    foreach($request->getPost()->periodY as $year):
                        foreach($request->getPost()->periodM as $month):
                            if($whereM2 == "") {
                                $whereM2 .= " AND ( ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            } else {
                                $whereM2 .= " OR ( EXTRACT(YEAR FROM date) = ".$year;
                                $whereM2 .= " AND EXTRACT(MONTH FROM date) = ".$month.")";
                            }
                        endforeach;
                        $counterA++;
                    endforeach;
                } else { ///// Année
                    if($whereM2 == "") {
                        $whereM2 .= " AND ( EXTRACT(YEAR FROM date) = ".$year;
                    } else {
                        $whereM2 .= " OR EXTRACT(YEAR FROM date) = ".$year;
                    }
                    $counterA++;
                }

                if($counterA == count($request->getPost()->periodY)){
                    $whereM2 .= " )";
                }
            } else {
                if($request->getPost()->periodM){ ///// Mois
                    $counterM = 0;
                    foreach($request->getPost()->periodM as $month):
                        if($whereM2 == "") {
                            $whereM2 .= " AND ( EXTRACT(MONTH FROM date) = ".$month;
                        } else {
                            $whereM2 .= " OR EXTRACT(MONTH FROM date) = ".$month;
                        }
                        $counterM++;
                    endforeach;

                    if($counterM == count($request->getPost()->periodM)){
                        $whereM2 .= " )";
                    }
                }
            }
        }

        $whereM .= $whereM2;
        $whereS .= $whereS2;

        $query['whereS'] = $whereS;
        $query['whereM'] = $whereM;
        $query['whereT'] = $whereT;

        return $query;        
    }
    
    public function echoShortDayText()
    {
        $dayText[0] = 'dim';
        $dayText[1] = 'lun';
        $dayText[2] = 'mar';
        $dayText[3] = 'mer';
        $dayText[4] = 'jeu';
        $dayText[5] = 'ven';
        $dayText[6] = 'sam';        
        
        return $dayText;
    }
    
    public function echoLongDayText()
    {
        $dayText[0] = 'dimanche';
        $dayText[1] = 'lundi';
        $dayText[2] = 'mardi';
        $dayText[3] = 'mercredi';
        $dayText[4] = 'jeudi';
        $dayText[5] = 'vendredi';
        $dayText[6] = 'samedi';        
        
        return $dayText;
    }
    
    public function echoMonthText()
    {
        $monthText[1] = 'Janvier';
        $monthText[2] = 'Fevrier';
        $monthText[3] = 'Mars';
        $monthText[4] = 'Avril';
        $monthText[5] = 'Mai';
        $monthText[6] = 'Juin';
        $monthText[7] = 'Juillet';
        $monthText[8] = 'Août';
        $monthText[9] = 'Septembre';
        $monthText[10] = 'Octobre';
        $monthText[11] = 'Novembre';
        $monthText[12] = 'Décembre';
        
        return $monthText;
    }
}
?>
